from sqlalchemy import exc, or_, and_, desc
from sqlalchemy.orm import joinedload, Session

from model.TeachingModel import ClassModel
from schema.QuerySchema import QueryBase, ClassQuery, ClassByStatusListQuery, ClassByStatusAndPageQuery
from schema.TeachingSchema import ClassInfo
from util.ErrorCode import ErrorCode
from util.LogHelper import Log


def add_class(classInfo: ClassInfo, db: Session):
    classModel = ClassModel(class_name=classInfo.class_name, class_type=classInfo.class_type,
                            course_id=classInfo.course_id, teacher_id=classInfo.teacher_id,
                            lecturer_id=classInfo.lecturer_id, start_date=classInfo.start_date,
                            end_date=classInfo.end_date, status=1)
    try:
        db.add(classModel)
        db.commit()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
    except exc.SQLAlchemyError as e:
        db.rollback()
        Log.getlogger().error(f'add_class failed:{classInfo} message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def mod_class(classInfo: ClassInfo, db: Session):
    classModel = db.query(ClassModel).filter(ClassModel.id == classInfo.id).one_or_none()
    if classModel is not None:
        # 如果班级状态为已完结，不允许修改
        if classModel.status == 3:
            return {'code': ErrorCode.CLASS_ALREADY_FINISHED.value, 'message': 'class already finished,can not mod!'}
        else:
            if classInfo.class_name is not None:
                classModel.class_name = classInfo.class_name if len(classInfo.class_name) != 0 else classModel.class_name
            if classInfo.class_type != 0:
                classModel.class_type = classInfo.class_type
            if classInfo.course_id != 0:
                classModel.course_id = classInfo.course_id
            if classInfo.teacher_id != 0:
                classModel.teacher_id = classInfo.teacher_id
            if classInfo.lecturer_id != 0:
                classModel.lecturer_id = classInfo.lecturer_id
            if classInfo.start_date is not None:
                classModel.start_date = classInfo.start_date if len(classInfo.start_date) != 0 else classModel.start_date
            if classInfo.end_date is not None:
                classModel.end_date = classInfo.end_date if len(classInfo.start_date) != 0 else classModel.end_date
            if classInfo.status != 0:
                classModel.status = classInfo.status
            try:
                db.commit()
                return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
            except exc.SQLAlchemyError as e:
                Log.getlogger().error(f'mod_class failed:{classInfo} message: {e}')
                return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
    else:
        Log.getlogger().error(f'id:{classInfo.id} is not exists!')
        return {'code': ErrorCode.DATABASE_RECORD_NOT_FOUND.value, 'message': 'record not exists!'}


def query_class_by_page(query_base: QueryBase, db: Session):
    try:
        total_count = db.query(ClassModel).count()
        total_pages = (total_count + query_base.limit - 1) // query_base.limit

        result = db.query(ClassModel).options(joinedload(ClassModel.teacher_info), joinedload(ClassModel.lecturer_info), joinedload(ClassModel.course_info)).order_by(desc(ClassModel.start_date)).offset((query_base.skip - 1) * query_base.limit).limit(query_base.limit).all()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'total_count': total_count, 'total_pages': total_pages, 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_class_by_page failed! message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}



def query_class_all_students(db: Session):
    try:
        result = db.query(ClassModel).options(joinedload(ClassModel.students)).order_by(desc(ClassModel.start_date)).all()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_class_all failed! message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}

#class_type、course_id、status查询course
def query_class_by_field(classQuery: ClassQuery, db: Session):
    try:
        total_count = db.query(ClassModel).filter(and_(
            or_(ClassModel.class_type == classQuery.class_type, classQuery.class_type == 0),
            or_(ClassModel.course_id == classQuery.course_id, classQuery.course_id == 0),
            or_(ClassModel.status == classQuery.status, classQuery.status == 0)
        )).count()
        total_pages = (total_count + classQuery.query_base.limit - 1) // classQuery.query_base.limit

        result = db.query(ClassModel).options(joinedload(ClassModel.teacher_info), joinedload(ClassModel.lecturer_info), joinedload(ClassModel.course_info)).filter(and_(
            or_(ClassModel.class_type == classQuery.class_type, classQuery.class_type == 0),
            or_(ClassModel.course_id == classQuery.course_id, classQuery.course_id == 0),
            or_(ClassModel.status == classQuery.status, classQuery.status == 0)
        )).order_by(desc(ClassModel.start_date)).offset((classQuery.query_base.skip - 1) * classQuery.query_base.limit).limit(classQuery.query_base.limit).all()

        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'total_count': total_count, 'total_pages': total_pages, 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_class_by_field failed! {classQuery}  message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}

def query_class_all_by_status(classByStatusListQuery: ClassByStatusListQuery, db: Session):
    try:
        result = db.query(ClassModel).filter(ClassModel.status.in_(classByStatusListQuery.status_list)).order_by(
            desc(ClassModel.start_date)).all()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_class_all_by_status failed! message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def query_class_all_by_status_and_page(classByStatusAndPageQuery: ClassByStatusAndPageQuery, db: Session):
    try:
        total_count = db.query(ClassModel).filter(ClassModel.status.in_(classByStatusAndPageQuery.status_list)).count()
        total_pages = (total_count + classByStatusAndPageQuery.query_base.limit - 1) // classByStatusAndPageQuery.query_base.limit

        result = db.query(ClassModel).options(joinedload(ClassModel.teacher_info), joinedload(ClassModel.lecturer_info), joinedload(ClassModel.course_info)).filter(ClassModel.status.in_(classByStatusAndPageQuery.status_list)).order_by(
            desc(ClassModel.start_date)).offset((classByStatusAndPageQuery.query_base.skip - 1) * classByStatusAndPageQuery.query_base.limit).limit(classByStatusAndPageQuery.query_base.limit).all()
        return {'code': ErrorCode.SUCCESS.value, 'total_count': total_count, 'total_pages': total_pages,  'message': 'success', 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_class_all_by_status failed! message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
